city_code<-'qd' #目标城市
pty<-11    #产品类型
# date_base<-24109  #基期设定

library(RMySQL)
con <- dbConnect(MySQL(),host="192.168.3.139",
                 dbname="niek_160906",user="niek",password="FbWf8AKC")  
dbSendQuery(con,'set names utf8')

#抽取数据
source('R/load_spatial_data.R')
# price_sql<-paste0("select city_code,ymid,ha_code,proptype,saleprice,salebldgarea,salecount from ha_price where city_code=\'",city_code,"\'","and proptype=",pty)
# price_sale<-dbGetQuery(con,price_sql)
# info<-dbGetQuery(con,paste0("select * from ha_info where city_code=\'",city_code,"\'"))
# bldg<-dbGetQuery(con,paste0("select * from ha_bldg where city_code=\'",city_code,"\'"))
# phase<-dbGetQuery(con,paste0("select * from ha_phase where city_code=\'",city_code,"\'"))

#ha_price与ha_phase数据处理
library(dplyr)
price_sale<-subset(tabln.vec$ha_price,proptype==pty)[,c('ymid','ha_code','saleprice','salebldgarea')]%>%na.omit
phase<-tabln.vec$ha_phase%>%
    filter(!is.na(buildyear))%>%
    group_by(ha_code)%>%
    summarise(buildyear=round(mean(buildyear)))%>%as.data.frame

#关联phase数据
pp_sale<-merge(price_sale,phase,by='ha_code',all.x=TRUE)

#关联bldg数据
# library(nnet)
# bldg2<-cbind(bldg,class.ind(bldg$bldg_type))
# pib_sale<-merge(pi_sale,bldg2[,c(-1,-3,-4)],by='ha_code',all.x=TRUE)

#关联POI与info数据
ppi_sale<-merge(pp_sale,ha_info.sp,by='ha_code',all.x=TRUE)
myvar<-names(ppi_sale) %in% c('city_code','ha_code','proptype','salecount','name','ha_cl_code','ha_cl_name','dist_code','dist_name','x','y')
sale<-ppi_sale[!myvar]


#探索缺失值
# library(mice)
# md.pattern(data)
# mean(is.na(sale$greening_rate))
# sapply(sale,function(x) mean(is.na(x)))

#计数
#sum(table(unique(pi_sale$ha_code)))

#处理缺失值：随机森林迭代法
# library(missForest)
# data.full<-missForest(data)$ximp

#建模
date<-sort(unique(ppi_sale$ymid))
len<-length(date)
fit.vec<-vector(mode='list',len)
names(fit.vec)<-date
for (i in 1:len){
    sale.new<-subset(sale,ymid==date[i])
    fit.vec[[i]]<-lm(saleprice~.,data = sale.new[,names(sale.new)!='ymid'])
}

#计算HPI（拉氏指数）
data_base<-sale[1,-1]
data_base1<-subset(sale,ymid==date[1]) #基期设定为最早日期

len_base<-length(names(data_base))
for(ii in 1:len_base){
    name_base<-names(data_base)[ii]
    data_base[,name_base]<-mean(data_base1[,name_base],na.rm=T)
}

result<-as.data.frame(cbind(dt<-rep(0,len),price.pre<-rep(0,len),r2<-rep(0,len),FBPI<-rep(0,len)))
names(result)<-c('DT','PRE','AdjR2','FBPI')
for(j in 1:len){
    price.pre<-predict(fit.vec[[j]],newdata=data_base)
    fit.sum<-summary(fit.vec[[j]])
    fit.squ<-fit.sum$adj.r.squared
    result[j,1]<-date[j]
    result[j,2]<-price.pre[[1]]
    result[j,3]<-fit.squ
    result[j,4]<-result[j,2]/result[1,2]*100
}

file_name<-paste0(city_code,'_sale',pty)
file.save<-paste0("~/mnt/HPI/",file_name,"new.csv")
write.csv(result,file.save)
